"""
Case Type   : synchronous_commit
Case Name   : 使用set方式设置synchronous_commit为off，设置成功
Create At   : 2023/8/16
Owner       : li-xin12345
Description :
    1、查询synchronous_commit默认值
    2、设置数据库级别，synchronous_commit=off
    3、设置用户级别，synchronous_commit=off
    4、设置会话级别，synchronous_commit=off
Expect      :
    1、成功
    2、下次会话中生效
    3、下次会话中生效
    4、当前会话中生效
History     :
"""

import os
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

Pri_SH = CommonSH('PrimaryDbUser')
Node_Num = Pri_SH.get_node_num()


@unittest.skipIf(3 > Node_Num, '不满足一主两备环境跳过')
class SynchronousCommitTest(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_user = Node('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.tb_name = 't_china_mobile_0003'
        self.db_name = 'db_china_mobile_0003'
        self.user = 'u_china_mobile_0003'
        self.set_params = ['off', 'false', 'no', 0, 'FALSE', 'OFF']

    def test_synchronous_commit(self):
        text = '-----step1:查询synchronous_commit默认值;expect:成功-----'
        self.log.info(text)
        self.default_param = self.common.show_param('synchronous_commit')
        self.log.info(self.default_param)
        self.assertIn('on', self.default_param, '执行失败：' + text)

        text = '-----step2:设置数据库级别，synchronous_commit=off;' \
               'expect:下次会话中生效-----'
        self.log.info(text)
        cmd = f'drop database if exists {self.db_name}; ' \
              f'create database {self.db_name};'
        res = Pri_SH.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.CREATE_DATABASE_SUCCESS, res,
                      '执行失败：' + text)
        for i, item in enumerate(self.set_params):
            sql = f'alter database {self.db_name} set ' \
                  f'synchronous_commit to {item};'
            set_res = Pri_SH.execut_db_sql(sql)
            self.log.info(set_res)
            self.assertIn('ALTER DATABASE', set_res, '执行失败：' + text)
            res = Pri_SH.execut_db_sql('show synchronous_commit',
                                            dbname=f'{self.db_name}')
            self.log.info(res)
            self.assertIn('off', res, '执行失败：' + text)
            sql = f'alter database {self.db_name} set ' \
                  f'synchronous_commit to on;'
            set_res = Pri_SH.execut_db_sql(sql)
            self.log.info(set_res)
            self.assertIn('ALTER DATABASE', set_res, '执行失败：' + text)

        text = '-----step3:设置用户级别，synchronous_commit=off;' \
               'expect:下次会话中生效-----'
        self.log.info(text)
        cmd = f"drop user if exists {self.user};" \
              f"create user {self.user} with password '{macro.COMMON_PASSWD}';"
        self.log.info(cmd)
        res = Pri_SH.execut_db_sql(cmd)
        self.log.info(res)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, res,
                      '执行失败：' + text)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, res,
                      '执行失败：' + text)
        for i in self.set_params:
            sql = f'alter user {self.user} set synchronous_commit to {i}; '
            set_res = Pri_SH.execut_db_sql(sql)
            self.log.info(set_res)
            self.assertIn('ALTER ROLE', set_res, '执行失败：' + text)
            cmd = f'''source {macro.DB_ENV_PATH};
            gsql -d {self.pri_user.db_name} -p {self.pri_user.db_port} \
            -U {self.user} -W {self.pri_user.db_password} \
            -c "show synchronous_commit;"
            '''
            res = self.pri_user.sh(cmd).result()
            self.log.info(res)
            self.assertIn('off', res, '执行失败：' + text)

            sql = f'alter user {self.user} set synchronous_commit to on; '
            set_res = Pri_SH.execut_db_sql(sql)
            self.log.info(set_res)
            self.assertIn('ALTER ROLE', set_res, '执行失败：' + text)

        text = '-----step4:设置会话级别，synchronous_commit=off;' \
               'expect:当前会话中生效-----'
        self.log.info(text)
        for i in self.set_params:
            sql = f'set synchronous_commit to {i}; show synchronous_commit;'
            set_res = Pri_SH.execut_db_sql(sql)
            self.log.info(set_res)
            self.assertIn('SET', set_res, '执行失败：' + text)
            self.assertIn('off', set_res, '执行失败：' + text)
            res = Pri_SH.execute_gsguc(
                'reload', self.constant.GSGUC_SUCCESS_MSG,
                'synchronous_commit=on')
            self.log.info(res)
            self.assertTrue(res)

    def tearDown(self):
        self.log.info('-----恢复默认值-----')
        cmd = f'drop database if exists {self.db_name};' \
              f'drop user if exists {self.user}'
        res = Pri_SH.execut_db_sql(cmd)
        self.log.info(res)
        curr_param = self.common.show_param('synchronous_commit')
        if curr_param != self.default_param:
            res = Pri_SH.execute_gsguc(
                'reload', self.constant.GSGUC_SUCCESS_MSG,
                f'synchronous_commit={self.default_param}')
            self.log.info(res)
            self.assertTrue(res)
        self.assertIn(self.constant.DROP_DATABASE_SUCCESS, res)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, res)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')